package br.com.siscov.model.funcionario.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import br.com.siscov.exceptions.IntegrationException;
import br.com.siscov.model.conexao.DriverManager;
import br.com.siscov.model.vo.MotoristaVo;
import br.com.siscov.util.Utilitario;

public class MotoristaDAO extends DriverManager implements MotoristaIntefaceDAO {

	
	public void incluirFuncionarioMotorista(Connection connection, MotoristaVo motoristaVo) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(Boolean.FALSE);

			String query = "insert into funcionario(matricula,nome,dataNasc,telefone,sexo,rg,orgEmissor,cpf,escolaridade,cursos,cartTrabalho,logradouro,numero,cep,bairro,subBairro,dataAdm,valorPassagem,salario,cargo)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			statement = connection.prepareStatement(query);
			int i = 1;
			statement.setInt(i, motoristaVo.getMatricula());
			statement.setString(++i, motoristaVo.getNome());
			statement.setDate(++i, new Date(motoristaVo.getDatanasc().getTime()));
			statement.setString(++i, motoristaVo.getTelefone());
			statement.setString(++i, motoristaVo.getSexo());
			statement.setLong(++i, motoristaVo.getRg());
			statement.setString(++i, motoristaVo.getOrgEmissor());
			statement.setString(++i, motoristaVo.getCpf());
			statement.setString(++i, motoristaVo.getEscolaridade());
			statement.setString(++i, motoristaVo.getCursos());
			statement.setInt(++i, motoristaVo.getNumCartTrabalho());
			statement.setString(++i, motoristaVo.getLogradouro());
			statement.setInt(++i, motoristaVo.getNumero());
			statement.setString(++i, motoristaVo.getCep());
			statement.setString(++i, motoristaVo.getBairro());
			statement.setString(++i, motoristaVo.getSubBairro());
			statement.setDate(++i, new Date(motoristaVo.getDataAdmissao().getTime()));
			statement.setDouble(++i, motoristaVo.getVlpassagem().doubleValue());
			statement.setDouble(++i, motoristaVo.getSalario().doubleValue());
			statement.setString(++i, motoristaVo.getCargo());

			statement.executeUpdate();

			connection.commit();

		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}


		
	}

	public void incluirMotorista(Connection connection,MotoristaVo motoristaVo)throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);

			String query = "insert into motorista (cartMotorista,Funcionario_matricula,valCarteira)values(?,?,?);";
			statement = connection.prepareStatement(query);

			statement.setInt(1, motoristaVo.getCartMotorista());
			statement.setInt(2, motoristaVo.getMatricula());
			statement.setDate(3,new Date(motoristaVo.getValCarteira().getTime()));
			
			statement.executeUpdate();

			connection.commit();

		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}
		
	}

	
	public MotoristaVo obterMotorista(Integer matricula) throws IntegrationException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		MotoristaVo motoristaVo = null;

		try {

			connection = obterConexao();
			String query = "select matricula,nome,dataNasc,telefone,sexo,rg,orgEmissor,cpf,escolaridade,cursos,cartTrabalho,logradouro,numero,cep,bairro,subBairro,dataAdm,valorPassagem,salario,cargo from funcionario where matricula=?";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);

			resultSet = statement.executeQuery();
			
			motoristaVo = new MotoristaVo();
			
			if (resultSet.next()) {

				motoristaVo.setMatricula(new Integer("matricula"));
				motoristaVo.setNome("nome");
				motoristaVo.setDatanasc(Utilitario.formataData("dataNasc"));
				motoristaVo.setTelefone("telefone");
				motoristaVo.setSexo("sexo");
				motoristaVo.setRg(new Long("rg"));
				motoristaVo.setOrgEmissor("orgEmissor");
				motoristaVo.setCpf("cpf");
				motoristaVo.setEscolaridade("escolaridade");
				motoristaVo.setCursos("cursos");
				motoristaVo.setNumCartTrabalho(new Integer("numCartTrabalho"));
				motoristaVo.setLogradouro("logradouro");
				motoristaVo.setNumero(new Integer("numero"));
				motoristaVo.setCep("cep");
				motoristaVo.setBairro("bairro");
				motoristaVo.setSubBairro("subBairro");
				motoristaVo.setDataAdmissao(Utilitario.formataData("dataAdmissao"));
				motoristaVo.setVlpassagem(new Double("vlpassagem"));
				motoristaVo.setSalario(new Double("salario"));
				motoristaVo.setCargo("cargo");

			}
			return motoristaVo;

		} catch (Exception sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
			closeConnection(connection);
		}
}


	public void excluirMotorista(Connection connection, Integer matricula, Integer cartMotorista) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);
			String query = "delete from motorista where cartMotorista=? and Funcionario_matricula=?";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);
			statement.setInt(2,cartMotorista);
			connection.commit();
			
			statement.executeUpdate();

		} catch (Exception sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}
	}

	@Override
	public void excluirFuncionarioMotorista(Connection connection, Integer matricula) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);
			String query = "delete from funcionario where matricula=?";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);
			connection.commit();
			
			statement.executeUpdate();

		} catch (Exception sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}
	}
}
